AWS - RDS PostgreSQL

RDS PostgreSQL agent setup

DBtune offers a database monitoring agent that operates within your network, securely transmitting metric data to DBtune and applying new proposed configurations. Additionally, the agent is available as a ready-to-use Docker image hosted on public repositories.

Required permissions

Before starting the DBtune agent, you need to make sure that the following prerequisites are met:

Install pg_stat_statements

CREATE EXTENSION pg_stat_statements;

Create DBtune user in RDS PostgreSQL

CREATE USER dbtune WITH PASSWORD '{dbtune_password}';

Grant the necessary permissions to the DBtune user

GRANT pg_monitor TO dbtune;

AWS Parameter group for RDS

Create a custom parameter group and assign it to the RDS database instance. DBtune will tune the database parameters through the parameter group, we recommend to have one custom parameter group per database that will be tuned through DBtune.

AWS IAM permissions

The policy below contains the permissions required by the agent to collect metrics and perform tuning. The policy can be applied through a role granted to an IAM user or anywhere across the AWS login chain. The rds:RebootDBInstance permission is only required if you want to perform tuning with restart.

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "DescribeRDSInstanceTypes",
            "Effect": "Allow",
            "Action": "ec2:DescribeInstanceTypes",
            "Resource": "*"
        },
        {
            "Sid": "DescribeAndModifyDBInstance",
            "Effect": "Allow",
            "Action": [
                "rds:DescribeDBInstances",
                "rds:ModifyDBInstance"
            ],
            "Resource": "*"
        },
        {
            "Sid": "GetDBMetrics",
            "Effect": "Allow",
            "Action": [
                "cloudwatch:GetMetricStatistics",
                "pi:GetResourceMetrics"
            ],
            "Resource": "*"
        },
        {
            "Sid": "ModifyDBParameterGroup",
            "Effect": "Allow",
            "Action": [
                "rds:ModifyDBParameterGroup"
            ],
            "Resource": "*"
        },
        {
            "Sid": "CreateDBParameterGroup",
            "Effect": "Allow",
            "Action": [
                "rds:CreateDBParameterGroup"
            ],
            "Resource": "*"
        },
        {
            "Sid": "RebootDBInstance",
            "Effect": "Allow",
            "Action": [
                "rds:RebootDBInstance"
            ],
            "Resource": "*"
        }
    ]
}

Setting up the agent

There are multiple ways to run the DBtune agent, below is an example using Docker container:

docker run --detach \
  -e DBT_POSTGRESQL_CONNECTION_URL={connection url to the database} \
  -e DBT_RDS_DATABASE_IDENTIFIER={rds_database_identifier} \
  -e DBT_RDS_PARAMETER_GROUP_NAME={parameter_group_name} \
  -e DBT_AWS_ACCESS_KEY_ID={aws_access_key_id} \
  -e DBT_AWS_SECRET_ACCESS_KEY={aws_secret_access_key} \
  -e DBT_AWS_REGION={aws_region} \
  -e DBT_DBTUNE_SERVER_URL=https://app.dbtune.com \
  -e DBT_DBTUNE_API_KEY=your-api-key \
  -e DBT_DBTUNE_DATABASE_ID=your-database-id \
  public.ecr.aws/dbtune/dbtune/agent:latest --rds

Visit the DBtune agent repository for more information.

Environment variables

Variable	Description
`DBT_POSTGRESQL_CONNECTION_URL`	Connection URL to the RDS PostgreSQL database.
`DBT_RDS_DATABASE_IDENTIFIER`	Identifier of the RDS instance you want to optimize.
`DBT_RDS_PARAMETER_GROUP_NAME`	Name of the parameter group for the instance.
`DBT_AWS_ACCESS_KEY_ID`	AWS access key ID that DBtune will use to authenticate with AWS services.
`DBT_AWS_SECRET_ACCESS_KEY`	AWS secret access key that DBtune will use to authenticate with AWS services.
`DBT_AWS_REGION`	AWS region where your database is located.
`DBT_DBTUNE_SERVER_URL`	URL of the DBtune server
`DBT_DBTUNE_API_KEY`	Your DBtune API key for authentication.
`DBT_DBTUNE_DATABASE_ID`	Database ID assigned by DBtune for this database instance.

Parameters tuned

The parameters tuned by DBtune change with respect to the chosen tuning mode. Below are the two tuning modes supported.

Reload-only tuning mode

This tuning mode tunes without restarting the database server and using only reloads. This does not cause any downtime and is suitable for critical production environments.

The following parameters are tuned in this mode:

work_mem
random_page_cost
seq_page_cost
checkpoint_completion_target
effective_io_concurrency
max_parallel_workers_per_gather
max_parallel_workers
max_wal_size
min_wal_size
bgwriter_lru_maxpages
bgwriter_delay

Restart tuning mode

Most of our user base prefer reload-only tuning to avoid disruptions in production. If your system can handle restarts gracefully and you want to tune more parameters, you can choose this mode which restarts the database up to 30 times in a span of few hours; This will likely deliver additional performance. The following parameters are tuned in this mode in addition to the parameters tuned in the reload-only mode:

shared_buffers
max_worker_processes

Optimization objectives

The recommended objective to use is average query runtime (AQR), which DBtune will minimize as it suggests configurations. For additional metrics, please refer to our section on choosing an alternative optimization objectives.

Agent monitoring stats

info

DBtune only retrieves performance metrics from the database and AWS metric providers, it does not access or transmit any sensitive data, e.g., metadata and the tables data are not transferred.

Below is the data DBtune collects and sends to the DBtune server every second. It gathers these metrics by reading from system tables, as well as calling the AWS metric providers.

Collected data

Category	Metrics
Database performance	Query runtime, Transactions per second, Active connections, Cache hit ratio, Wait events, Autovacuum count
System	Database size, Server Uptime
Hardware	CPU utilization, Memory usage, Freeable memory

DBtune queries system metrics to ensure production safety. This takes place in 5 second intervals.

RDS PostgreSQL agent setup​

Required permissions​

AWS Parameter group for RDS​

AWS IAM permissions​

Setting up the agent​

Environment variables​

Parameters tuned​

Reload-only tuning mode​

Restart tuning mode​

Optimization objectives​

Agent monitoring stats​